package com.smartaccounting.dao;

import com.smartaccounting.model.Expense;
import com.smartaccounting.util.DatabaseUtil;

import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class ExpenseDAO {

    // 根据ID获取支出记录
    public Expense getExpenseById(int expenseId) throws SQLException {
        String sql = "SELECT * FROM expenses WHERE expense_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, expenseId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return extractExpenseFromResultSet(rs);
            }
        }
        return null;
    }

    // 获取用户的所有支出记录
    public List<Expense> getExpensesByUserId(int userId) throws SQLException {
        List<Expense> expenses = new ArrayList<>();
        String sql = "SELECT * FROM expenses WHERE user_id = ? ORDER BY expense_date DESC";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                expenses.add(extractExpenseFromResultSet(rs));
            }
        }
        return expenses;
    }

    // 获取用户指定时间范围内的支出记录
    public List<Expense> getExpensesByUserIdAndDateRange(int userId, LocalDate startDate, LocalDate endDate) throws SQLException {
        List<Expense> expenses = new ArrayList<>();
        String sql = "SELECT * FROM expenses WHERE user_id = ? AND expense_date BETWEEN ? AND ? ORDER BY expense_date DESC";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setDate(2, Date.valueOf(startDate));
            stmt.setDate(3, Date.valueOf(endDate));
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                expenses.add(extractExpenseFromResultSet(rs));
            }
        }
        return expenses;
    }

    // 获取用户指定月份的支出记录
    public List<Expense> getExpensesByUserIdAndMonth(int userId, String monthYear) throws SQLException {
        List<Expense> expenses = new ArrayList<>();
        String sql = "SELECT * FROM expenses WHERE user_id = ? AND DATE_FORMAT(expense_date, '%Y-%m') = ? ORDER BY expense_date DESC";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setString(2, monthYear);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                expenses.add(extractExpenseFromResultSet(rs));
            }
        }
        return expenses;
    }

    // 添加支出记录
    public boolean addExpense(Expense expense) throws SQLException {
        String sql = "INSERT INTO expenses (user_id, amount, category, payment_method, expense_date, description) VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, expense.getUserId());
            stmt.setBigDecimal(2, expense.getAmount());
            stmt.setString(3, expense.getCategory());
            stmt.setString(4, expense.getPaymentMethod());
            stmt.setDate(5, Date.valueOf(expense.getExpenseDate()));
            stmt.setString(6, expense.getDescription());
            return stmt.executeUpdate() > 0;
        }
    }

    // 更新支出记录
    public boolean updateExpense(Expense expense) throws SQLException {
        String sql = "UPDATE expenses SET amount=?, category=?, payment_method=?, expense_date=?, description=? WHERE expense_id=?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setBigDecimal(1, expense.getAmount());
            stmt.setString(2, expense.getCategory());
            stmt.setString(3, expense.getPaymentMethod());
            stmt.setDate(4, Date.valueOf(expense.getExpenseDate()));
            stmt.setString(5, expense.getDescription());
            stmt.setInt(6, expense.getExpenseId());
            return stmt.executeUpdate() > 0;
        }
    }

    // 删除支出记录
    public boolean deleteExpense(int expenseId) throws SQLException {
        String sql = "DELETE FROM expenses WHERE expense_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, expenseId);
            return stmt.executeUpdate() > 0;
        }
    }

    // 获取用户总支出金额
    public BigDecimal getTotalExpenseByUserId(int userId) throws SQLException {
        String sql = "SELECT SUM(amount) FROM expenses WHERE user_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                BigDecimal total = rs.getBigDecimal(1);
                return total != null ? total : BigDecimal.ZERO;
            }
        }
        return BigDecimal.ZERO;
    }

    // 获取用户指定月份的总支出
    public BigDecimal getTotalExpenseByUserIdAndMonth(int userId, String monthYear) throws SQLException {
        String sql = "SELECT SUM(amount) FROM expenses WHERE user_id = ? AND DATE_FORMAT(expense_date, '%Y-%m') = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setString(2, monthYear);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                BigDecimal total = rs.getBigDecimal(1);
                return total != null ? total : BigDecimal.ZERO;
            }
        }
        return BigDecimal.ZERO;
    }

    // 获取支出分类统计
    public List<Object[]> getExpenseCategorySummary(int userId, String monthYear) throws SQLException {
        List<Object[]> summary = new ArrayList<>();
        String sql = "SELECT category, SUM(amount) as total FROM expenses WHERE user_id = ? AND DATE_FORMAT(expense_date, '%Y-%m') = ? GROUP BY category";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setString(2, monthYear);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Object[] row = new Object[2];
                row[0] = rs.getString("category");
                row[1] = rs.getBigDecimal("total");
                summary.add(row);
            }
        }
        return summary;
    }

    // 获取支付方式统计
    public List<Object[]> getPaymentMethodSummary(int userId, String monthYear) throws SQLException {
        List<Object[]> summary = new ArrayList<>();
        String sql = "SELECT payment_method, SUM(amount) as total FROM expenses WHERE user_id = ? AND DATE_FORMAT(expense_date, '%Y-%m') = ? GROUP BY payment_method";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setString(2, monthYear);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Object[] row = new Object[2];
                row[0] = rs.getString("payment_method");
                row[1] = rs.getBigDecimal("total");
                summary.add(row);
            }
        }
        return summary;
    }

    // 从结果集提取支出对象
    private Expense extractExpenseFromResultSet(ResultSet rs) throws SQLException {
        Expense expense = new Expense();
        expense.setExpenseId(rs.getInt("expense_id"));
        expense.setUserId(rs.getInt("user_id"));
        expense.setAmount(rs.getBigDecimal("amount"));
        expense.setCategory(rs.getString("category"));
        expense.setPaymentMethod(rs.getString("payment_method"));
        expense.setExpenseDate(rs.getDate("expense_date").toLocalDate());
        expense.setDescription(rs.getString("description"));
        expense.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
        return expense;
    }
}